﻿using System;
using System.Collections.Generic;
using System.Web;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Collections;
using System.Configuration;
using System.Text.RegularExpressions;
namespace gamexpert
{
    public class DataBroker
    {
        public static string getConnectionString()
        {
            Configuration rootWebConfig = System.Web.Configuration.WebConfigurationManager.OpenWebConfiguration("/gamexpert");
            System.Configuration.ConnectionStringSettings connString;
            KeyValueConfigurationElement e=rootWebConfig.AppSettings.Settings["testKey"];
            
            string constr = "";
            if (0 < rootWebConfig.ConnectionStrings.ConnectionStrings.Count)
            {
                connString = rootWebConfig.ConnectionStrings.ConnectionStrings["gamexpertConnectionString"];
                if (null != connString)
                    constr = connString.ConnectionString;
            }
            return constr;
        }
        public static DataSet getProductsByCat(string cat){
            string constr = getConnectionString();
            string sqlstr = "Select * from my_product where category=@cat order by createdon";
            SqlConnection con = new SqlConnection(constr);
            SqlDataAdapter da = new SqlDataAdapter(sqlstr, con);
            da.SelectCommand.Parameters.Add("@cat",SqlDbType.NVarChar,50);
            da.SelectCommand.Parameters["@cat"].Value = cat;
            DataSet ds = new DataSet();
            da.Fill(ds);
            return ds;
        }
        public static DataSet getAllProducts()
        {
            string constr = getConnectionString();
            SqlConnection con = new SqlConnection(constr);
            SqlDataAdapter da = new SqlDataAdapter("Select * from my_product", con);
            DataSet ds = new DataSet();
            da.Fill(ds);
            return ds;
        }
        public static DataSet getProductById(int id)
        {
            string constr = getConnectionString();
            SqlConnection con = new SqlConnection(constr);
            string sqlstr="Select * from my_product where id=@id";
            SqlDataAdapter da = new SqlDataAdapter(sqlstr,con);
            da.SelectCommand.Parameters.Add("@id", SqlDbType.Int);
            da.SelectCommand.Parameters["@id"].Value = id;
            DataSet ds = new DataSet();
            da.Fill(ds);
            return ds;
        }
        public static string removeDoubleSpace(string str)
        {
            //remove space
            string tmp = "";
            tmp = str.Trim();
            return Regex.Replace(str, @"\s+", " ");
        }
        public static List<string> removeUnwantedString(string[] keysarr)
        {
            string[] removeWord = { "a", "an", "the", "am", "is", "are", "was", "were", "i", "we", "you", "he", "she", "it", "they", "me", "my", "mine", "your", "yours", "him", "his", "its", "her", "them", "in", "on", "at", "of" };
            List<string> newkeyarr = new List<string>();
            foreach (string s in keysarr)
            {
                if (Array.IndexOf(removeWord, s) <= -1)
                {
                    newkeyarr.Add(s);
                }
            }
            return newkeyarr;
        }
        public static DataSet searchProducts(string keyword)
        {
            string newkey= removeDoubleSpace(keyword);
            string[] keysarr = newkey.Split(' ');
            List<string> newkeyarr = removeUnwantedString(keysarr);
            DataSet ds = new DataSet();
            if (newkeyarr.Count > 0)
            {
                string where = "";
                string tmp = "";
                int i = 0;
                foreach (string s in newkeyarr)
                {
                    tmp = "";
                    tmp += (i == 0) ? " where " : " OR ";
                    tmp += " title LIKE {0} OR shortdescription LIKE {0} OR description LIKE {0} OR category LIKE {0} OR publisher LIKE {0} OR developer LIKE {0} OR platform LIKE {0} OR keywords LIKE {0} ";
                    tmp = String.Format(tmp, "@s" + i);
                    where += tmp;
                    i++;
                }


                string constr = getConnectionString();
                SqlConnection con = new SqlConnection(constr);
                string sqlstr = "Select * from my_product " + where;
                SqlDataAdapter da = new SqlDataAdapter(sqlstr, con);

                i = 0;
                foreach (string s in newkeyarr)
                {
                    da.SelectCommand.Parameters.Add("@s" + i, SqlDbType.NVarChar);
                    da.SelectCommand.Parameters["@s" + i].Value = "%" + s + "%";
                    i++;
                }                
                da.Fill(ds);
            }
            return ds;
        }
        public static void addBillingShippingInfo()
        {
        }
    }
}